package com.sql_sys ;

import java.sql.* ;
import java.util.* ;

public class load_sys
{

    String _url = "jdbc:mysql://localhost:3306/school" ;

    String _username ;

    String _password ;

    Connection connection = null ;

    Statement stmt = null ;

    ResultSet rs = null ;

    public load_sys (String username , String password) throws ClassNotFoundException, SQLException
    {

        _username = username ;

        _password = password ;

        try 
        {

            Class.forName ("com.mysql.cj.jdbc.Driver") ;  // 加载 MySQL 驱动

            System.out.println ("数据库驱动加载成功") ;

        } 

        catch (ClassNotFoundException e) 
        {

            e.printStackTrace () ;

            System.out.println ("数据库驱动加载失败") ;

        }

        try
        {

            connection = DriverManager.getConnection (_url , _username , _password) ;

            stmt = connection.createStatement () ;

            System.out.println ("数据库链接成功") ;

        }

        catch (SQLException e)
        {

            e.printStackTrace () ;

            System.out.println ("数据库链接失败,请重新登录!") ;

        }
        
    }

    public void erase (Scanner input)
    {

        System.out.println () ;

        String sql ;

        ArrayList<String> count_string = new ArrayList<String> () ;

        ArrayList<Integer> count_int = new ArrayList<Integer> () ;

        String a = input.nextLine () ;

        while (true)
        {

            try 
            {

                sql = "show tables" ; 

                System.out.println () ;

                count_string = show_all (sql) ;

            } 

            catch (SQLException e) 
            {
                
                e.printStackTrace () ;

            }

            System.out.printf ("请选择你要删除的年级: ") ;

            String select = input.nextLine () ; 

            int flag = 0 ;

            for (int l = 0 ; l < count_string.size () ; l++)
            {

                if (count_string.get (l).equals("grade" + select))
                {

                    flag = 1;

                }

            }

            if (flag == 0)
            {

                System.out.println ("字符非法,请重新选择!") ;

                continue ;

            }

            System.out.printf ("1.删除整个年级 2.删除年级的学生: ") ;

            int i = input.nextInt () ;

            a = input.nextLine () ;

            String code ;

            switch (i) 
            {

                case 1 :

                    code = "drop table grade" + select ;

                    try
                    {

                        stmt.executeUpdate (code) ;

                        System.out.println ("删除年级成功!") ;

                    }

                    catch (SQLException e)
                    {

                        e.printStackTrace () ;

                    }
                    
                    break;

                case 2 :

                    code = "select * from grade" + select ;

                    try
                    {
        
                        count_int = show_table (code) ;
        
                    }
        
                    catch (SQLException e)
                    {
        
                        e.printStackTrace () ;
        
                    }

                    System.out.printf ("请输入要删除学生的学号: ") ;

                    String id = input.nextLine () ;

                    flag = 0 ;

                    for (int l = 0 ; l < count_int.size () ; l++)
                    {

                        if (Integer.parseInt(id) == count_int.get(l))
                        {

                            flag = 1 ;

                        }

                    }

                    if (flag == 0)
                    {

                        System.out.println ("非法字符!") ;

                        break ;

                    }

                    code = "delete from grade" + select + " where stu_id = " + id ;

                    try
                    {

                        stmt.executeUpdate (code) ;

                        System.out.println ("删除成功!") ;

                    }

                    catch (SQLException e)
                    {

                        e.printStackTrace () ;

                    }

                    break ;
            
                default:

                    System.out.println ("字符非法,请重新选择!") ;

                    continue ;

            }  

            System.out.printf ("1.继续 2.退出 : ") ;

            i = input.nextInt () ;

            a = input.nextLine () ;

            if (i == 2)
            {

                break ;

            }

        }

    }

    public ArrayList<String> show_all (String sql) throws SQLException
    {

        ArrayList<String> count = new ArrayList<String> ();

        rs = stmt.executeQuery (sql) ;

        while (rs.next ()) 
        {

            System.out.print (rs.getString(1)) ;

            System.out.println () ;

            count.add (rs.getString(1)) ;
            
        }

        return count ;

    }

    public ArrayList<Integer> show_table (String sql) throws SQLException
    {

        System.out.println () ;

        ArrayList<Integer> count = new ArrayList<Integer> () ;

        rs = stmt.executeQuery (sql) ;

        while (rs.next ())
        {

            int id = rs.getInt (1) ;

            String gender = rs.getString ("gender") ;

            String name = rs.getString ("stu_name") ;

            String phone = rs.getString ("phone") ;

            int class_id = rs.getInt ("class_id") ;

            String major = rs.getString ("stu_memo") ;
            
            System.out.println ("id: " + id + "     gender: " + gender + "      name: " + name + "      phone: " + phone + "    class: " + class_id + "     major: " + major) ;

            count.add (rs.getInt (1)) ;

        }

        return count ;
        
    }

    public ArrayList<String> stu_name (String sql) throws SQLException
    {

        ArrayList<String> count = new ArrayList<String> () ;

        rs = stmt.executeQuery (sql) ;

        while (rs.next ()) 
        {

            String name = rs.getString ("stu_name") ;

            count.add (name) ;
            
        }

        return count ;

    } 

    public void update (Scanner input)
    {

        ArrayList<String> count_string = new ArrayList<String> ();

        ArrayList<Integer> count_int = new ArrayList<Integer> () ;

        ArrayList<String> stu_name = new ArrayList<String> () ;

        int flag = 0 ;

        while (true) 
        {

            input.nextLine () ;

            try
            {

                count_string = show_all ("show tables") ;

            }

            catch (SQLException e)
            {

                e.printStackTrace () ;

            }

            System.out.printf ("请选择要更新的年级: ") ;

            String grade = input.nextLine () ;

            flag = 0 ;

            for (int l = 0 ; l < count_string.size () ; l++)
            {

                if (count_string.get (l).equals("grade" + grade))
                {

                    flag = 1;

                }

            }

            if (flag == 0)
            {

                System.out.println ("字符非法,请重新选择!") ;

                continue ;

            }

            try
            {

                count_int = show_table("select * from grade" + grade) ;

                stu_name = stu_name ("select * from grade" + grade) ;

            }

            catch (SQLException e)
            {

                e.printStackTrace () ;

            }

            System.out.printf ("1.通过学号修改 2.通过姓名修改: ") ;

            int select = input.nextInt () ;

            switch (select) 
            {

                case 1 :

                    System.out.printf ("请输入需要修改的学生的学号: ") ;

                    int s_id = input.nextInt () ;

                    flag = 0 ;

                    for (int l = 0 ; l < count_int.size () ; l++)
                    {

                        if (count_int.get (l) == s_id)
                        {

                            flag = 1;

                        }

                    }

                    if (flag == 0)
                    {

                        System.out.println ("字符非法,请重新选择!") ;

                        break ;

                    }

                    input.nextLine () ;

                    System.out.printf ("更新性别(F , M): ") ;

                    String gender = input.nextLine () ;

                    System.out.printf ("更新名字: ");

                    String name = input.nextLine () ;

                    System.out.printf ("更新手机号: ") ;

                    String phone = input.nextLine () ;

                    System.out.printf ("更新班级: ") ;

                    int class_id = input.nextInt () ;

                    input.nextLine () ;

                    System.out.printf ("更新科目: ") ;

                    String major = input.nextLine () ;

                    try
                    {

                        UpdateById(grade , s_id , gender , name , phone , class_id , major) ;

                        System.out.println ("更新成功!") ;

                    }

                    catch (SQLException e)
                    {

                        System.out.println ("更新失败!") ;

                        e.printStackTrace () ;

                    }
                    
                    break ;

                case 2 :

                    input.nextLine () ;

                    System.out.printf ("请输入需要修改的学生的姓名: ");

                    String s_name = input.nextLine () ;

                    flag = 0 ;

                    for (int l = 0 ; l < count_int.size () ; l++)
                    {

                        if (stu_name.get (l).equals(s_name))
                        {

                            flag = 1;

                        }

                    }

                    if (flag == 0)
                    {

                        System.out.println ("字符非法,请重新选择!") ;

                        break ;

                    }

                    System.out.printf ("更新性别(F , M): ") ;

                    String _gender = input.nextLine () ;

                    System.out.printf ("更新名字: ");

                    String _name = input.nextLine () ;

                    System.out.printf ("更新手机号: ") ;

                    String _phone = input.nextLine () ;

                    System.out.printf ("更新班级: ") ;

                    int _class_id = input.nextInt () ;

                    input.nextLine () ;

                    System.out.printf ("更新科目: ") ;

                    String _major = input.nextLine () ;

                    try
                    {

                        UpdateByName(grade , _gender, _name, _phone, _class_id, _major, s_name) ;

                        System.out.println ("更新成功!") ;

                    }

                    catch (SQLException e)
                    {

                        e.printStackTrace () ;

                        System.out.println ("更新失败!") ;

                    }

                    break ;
            
                default:

                    System.out.println ("字符非法,请重新选择!") ;

                    break ;

            }

            System.out.printf ("1.继续 2.退出: ") ;

            select = input.nextInt () ;

            if (select == 2)
            {

                break ;

            }
            
        }

    }

    public void UpdateById (String grade , int id , String sex , String name , String phone , int class_id , String major) throws SQLException
    {

        String sql = "update grade" + grade + " set gender = " + "'" + sex + "'" + ", stu_name = " + "\"" + name + "\" ," + " phone = " + "\"" + phone + "\" ," + " class_id = " + class_id + " , stu_memo = " + "\"" + major + "\"" + " where stu_id = " + id ;

        stmt.executeUpdate (sql) ;

    }

    public void UpdateByName (String grade , String sex , String name , String phone , int class_id , String major , String s_name) throws SQLException
    {

        String sql = "update grade" + grade + " set gender = " + "'" + sex + "'" + ", stu_name = " + "\"" + name + "\" ," + " phone = " + "\"" + phone + "\" ," + " class_id = " + class_id + " , stu_memo = " + "\"" + major + "\"" + " where stu_name = " + "\"" + s_name + "\"" ;

        stmt.executeUpdate (sql) ;

    }

    @Override
    protected void finalize () throws Throwable 
    {

        connection.close () ;

        stmt.close () ;

        rs.close () ;

    }

}